package com.homedo.odin.solution.service;


import com.homedo.core.common.util.DateUtils;
import com.homedo.core.common.util.HttpUtils;
import com.homedo.odin.solution.model.entity.SolProductDetail;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.security.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

/**
 * @author: zhaoxudong
 * @date: 2018/3/12 9:30
 * @Description: 解析文档服务类
 */
@Service
public class PoiServiceImpl {

    private static final Logger logger = LoggerFactory.getLogger(PoiServiceImpl.class);

    /**
     * 获取脏词列表
     *
     * @param sourceUrl excel地址
     */
    public Set getSensitiveWordSet(String sourceUrl) throws IOException {
        Workbook book = getSheets(sourceUrl);
        Sheet sheet = book.getSheetAt(0);
        Set<String> sensitiveWordSet = new HashSet<>();
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            row.getCell(0).setCellType(CellType.STRING);
            if (StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
                continue;
            }
            //脏词
            sensitiveWordSet.add(row.getCell(0).getStringCellValue().trim());
        }
        return sensitiveWordSet;
    }

    /**
     * 解析sheet并获取清单详情
     */
    public List<SolProductDetail> parseSheetGetSolList(Integer listId, Long userId, Sheet sheet, String userName) {
        List<SolProductDetail> details = new ArrayList<>();
        SolProductDetail detail;
        for (int i = 2; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            detail = new SolProductDetail();
            detail.setListId(listId);
            detail.setCreatedBy(userId);
            detail.setModifiedBy(userId);
            detail.setCreatedName(userName);
            detail.setModifiedName(userName);
            //系统分类
            if (row.getCell(0) != null) {
                row.getCell(0).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setSysSort(row.getCell(0) == null ? null : row.getCell(0).getStringCellValue());
            //二级分类
            if (row.getCell(1) != null) {
                row.getCell(1).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(1).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setSysType(row.getCell(1) == null ? null : row.getCell(1).getStringCellValue());
            //产品名称
            if (row.getCell(2) != null) {
                row.getCell(2).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(2).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setProductName(row.getCell(2) == null ? null : row.getCell(2).getStringCellValue());
            //型号
            if (row.getCell(3) != null) {
                row.getCell(3).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(3).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setModel(row.getCell(3) == null ? null : row.getCell(3).getStringCellValue());
            //品牌
            if (row.getCell(4) != null) {
                row.getCell(4).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(4).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setBrand(row.getCell(4) == null ? null : row.getCell(4).getStringCellValue());
            //sku编号
            if (row.getCell(5) != null) {
                row.getCell(5).setCellType(CellType.STRING);
            }
            detail.setSkuId(row.getCell(5) == null ? null : row.getCell(5).getStringCellValue());
            //数量
            if (row.getCell(6) != null) {
                row.getCell(6).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(6).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setAmount(row.getCell(6) == null ? null : row.getCell(6).getStringCellValue());
            //单位
            if (row.getCell(7) != null) {
                row.getCell(7).setCellType(CellType.STRING);
                if (StringUtils.isBlank(row.getCell(7).getStringCellValue())) {
                    continue;
                }
            } else {
                continue;
            }
            detail.setUnit(row.getCell(7) == null ? null : row.getCell(7).getStringCellValue());
            //参考价
            if (row.getCell(8) != null) {
                row.getCell(8).setCellType(CellType.STRING);
            }
            detail.setPrice(row.getCell(8) == null ? null : row.getCell(8).getStringCellValue());
            details.add(detail);
        }
        return details;
    }

    /**
     * 获取文件
     */
    public Workbook getSheets(String sourceUrl) throws IOException {
        logger.info("文件开始下载,地址：{}", sourceUrl);
        Workbook book = null;
        try {
            book = new HSSFWorkbook(HttpUtils.downLoadFromUrl(sourceUrl));
        } catch (OfficeXmlFileException e) {
            book = new XSSFWorkbook(HttpUtils.downLoadFromUrl(sourceUrl));
        }
        logger.info("文件开始下载完成");
        return book;
    }

    /**
     * @param fileName excel文件名
     */
    public void exportXlsx(String fileName, Workbook workbook, HttpServletResponse response) {

        response.setContentType("application/vnd.ms-excel");

        OutputStream outputStream = null;

        try {
            outputStream = response.getOutputStream();
            String fn = new String(fileName.getBytes(), "utf-8");
            response.setHeader("Content-disposition",
                    "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    /**
     * 导出数据
     */
    public Workbook exportXlsx(String sheetName, Map<String, String> headMap, List<Map<String, Object>> dataList) {

        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet(sheetName);

        int rowIndex = 0, columnIndex = 0;
        Set<String> keys = headMap.keySet();

        //表头
        Row row = sheet.createRow(rowIndex++);
        for (String key : keys) {
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(headMap.get(key));
        }

        //内容
        if (dataList != null && !dataList.isEmpty()) {
            for (Map<String, Object> map : dataList) {
                row = sheet.createRow(rowIndex++);
                columnIndex = 0;
                for (String key : keys) {
                    Cell cell = row.createCell(columnIndex++);
                    setCellValue(cell, map.get(key));
                }
            }
        }

        return workbook;
    }

    private void setCellValue(Cell cell, Object obj) {
        if (obj == null) {
            return;
        }
        if (obj instanceof String) {
            cell.setCellValue((String) obj);
        } else if (obj instanceof Date) {
            Date date = (Date) obj;
            if (date != null) {
                cell.setCellValue(DateUtils.formatDate(date));
            }
        } else if (obj instanceof Calendar) {
            Calendar calendar = (Calendar) obj;
            if (calendar != null) {
                cell.setCellValue(DateUtils.formatDate(calendar.getTime()));
            }
        } else if (obj instanceof Timestamp) {
            Timestamp timestamp = (Timestamp) obj;
            if (timestamp != null) {
                cell.setCellValue(DateUtils.formatDate(timestamp.getTimestamp()));
            }
        } else if (obj instanceof Double) {
            cell.setCellValue((Double) obj);
        } else {
            cell.setCellValue(obj.toString());
        }
    }

    public static void main(String[] args) {
        //String url = "http://devzxd.oss-cn-shanghai.aliyuncs.com/hmd/%E8%84%8F%E8%AF%8D%E5%BA%93%E6%A8%A1%E6%9D%BF.xlsx?Expires=1520821146&OSSAccessKeyId=TMP.AQFj6bjrM52uPuafNTUwFKWBLBmXdsyAZFRpG1H-kliDxFUwW3_LeoBWMjTsADAtAhQ5oJS1wKaRugA3KZW97IfxYIX7iwIVAIpuTU0lDrutzPLGA2sc7_AafxiR&Signature=%2Bj8wfhdcQW23ULEvadIJY2R2oy0%3D";
//        String url = "http://devzxd.oss-cn-shanghai.aliyuncs.com/hmd/%E8%84%8F%E8%AF%8D%E5%BA%93%E6%A8%A1%E6%9D%BF.xlsx?Expires=1520825095&OSSAccessKeyId=TMP.AQFj6bjrM52uPuafNTUwFKWBLBmXdsyAZFRpG1H-kliDxFUwW3_LeoBWMjTsADAtAhQ5oJS1wKaRugA3KZW97IfxYIX7iwIVAIpuTU0lDrutzPLGA2sc7_AafxiR&Signature=dvfAUEd3Tp0vjOaZC7Jw85KsEF0%3D";
//        try {
//            PoiUtil.getSensitiveWordSet(url);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
    }
}
